# -*- coding: utf-8 -*-
import random
import sqlite3

from util.initializ import con, cursor
from util.utils import *


def select_user_login_list():
    try:
        cursor.execute('select * from user_login_success order by id desc')
        raws = cursor.fetchall()
    except:
        return []
    # print(raws)
    return raws


def select_user_no_shiming_list():
    # print(1)
    cursor.execute('select * from user_login_success where is_sm="否"')
    raws = cursor.fetchall()
    # print(raws)
    return raws


def select_user_guoqi_list():
    # print(1)
    cursor.execute('select mobile,id from user_login_success where access_token="token已过期"')
    raws = cursor.fetchall()
    # print(raws)
    return raws


def select_proxy_list():
    cursor.execute('select * from proxy')
    raws = cursor.fetchall()
    # print(raws)
    return raws


def select_goods(goods_id):
    cursor.execute('select * from goods where goods_id={}'.format(goods_id))
    raws = cursor.fetchall()
    # print(raws)
    if raws:
        return raws[0]
    else:
        return None


def create_proxy(order, api_key, order_type):
    insert_sql = 'insert into proxy("order", "api_key", "order_type") values ("{}","{}","{}")'.format(
        order, api_key, order_type
    )
    try:
        cursor.execute(insert_sql)
    except sqlite3.IntegrityError:
        pass
    con.commit()
    return 1


def select_yuyue_success(mobile, goods_id):
    cursor.execute('select * from user_yuyue_success where mobile="{}" and goods_id={} '.format(mobile, goods_id))
    raws = cursor.fetchall()
    if raws:
        return raws[0]
    else:
        return None


def select_yuyue_success2(mobile, goods_id, pay_first_time, pay_last_time):
    cursor.execute(
        'select * from user_yuyue_success where mobile="{}" and goods_id={} and pay_first_time="{}" and pay_last_time= "{}"'.format(
            mobile, goods_id, pay_first_time, pay_last_time))
    raws = cursor.fetchall()
    if raws:
        return raws[0]
    else:
        return None


def select_yuyue_all():
    cursor.execute('select * from user_yuyue_success ')
    raws = cursor.fetchall()
    if not raws:
        raws = [('还没有已预约', '还没有已预约', '还没有已预约', '还没有已预约', '还没有已预约', '还没有已预约', '还没有已预约', '还没有已预约', '还没有已预约', '还没有已预约',
                 '还没有已预约',)]
    return raws


def create_yuyue_success(mobile, activity_id, goods_id, quantity, addr_id, pay_first_time, pay_last_time):
    stime = get_time_str(get_timestamp(pay_first_time) - 3)
    insert_sql = 'insert into user_yuyue_success(mobile, activity_id, goods_id,quantity,addr_id,pay_first_time,pay_last_time,is_qj_succ,qj_start_time) ' \
                 'values ({},"{}","{}","{}","{}","{}","{}","否","{}")'.format(mobile, activity_id, goods_id, quantity,
                                                                             addr_id, pay_first_time, pay_last_time,
                                                                             stime)
    try:
        cursor.execute(insert_sql)
    except:
        pass
    con.commit()
    return 1


def update_yuyue_success_stime(c_id, qj_start_time):
    insert_sql = 'update user_yuyue_success set qj_start_time="{}" where id="{}"'.format(
        qj_start_time, c_id
    )
    try:
        cursor.execute(insert_sql)
    except sqlite3.IntegrityError:
        pass
    con.commit()
    return 1


def update_yuyue_success(mobile, goods_id, pay_first_time, is_qj_succ):
    insert_sql = 'update user_yuyue_success set is_qj_succ="{}" where mobile="{}" and goods_id="{}" and pay_first_time="{}"'.format(
        is_qj_succ, mobile, goods_id, pay_first_time
    )
    try:
        cursor.execute(insert_sql)
    except sqlite3.IntegrityError:
        pass
    con.commit()
    return 1


def create_user_login_success(mobile, access_token, user_id, is_sm, jifen):
    insert_sql = 'insert into user_login_success(mobile, access_token, user_id,is_sm,point) ' \
                 'values ("{}","{}","{}","{}",{})'.format(
        mobile, access_token, user_id, is_sm, jifen
    )
    try:
        cursor.execute(insert_sql)
    except sqlite3.IntegrityError:
        pass
    con.commit()
    return 1


def select_goods2(goods_id, pay_first_time):
    cursor.execute('select * from goods where goods_id={} and pay_first_time="{}" '.format(goods_id, pay_first_time))
    raws = cursor.fetchall()
    # print(raws)
    return raws


def create_goods(goods_id, goods_name, goods_price, reserve_first_time, reserve_last_time,
                 pay_first_time, pay_last_time):
    insert_sql = 'insert into goods(goods_id, goods_name, goods_price,reserve_first_time,reserve_last_time,pay_first_time,pay_last_time) ' \
                 'values ({},"{}","{}","{}","{}","{}","{}")'.format(goods_id, goods_name, goods_price,
                                                                    reserve_first_time, reserve_last_time,
                                                                    pay_first_time, pay_last_time)
    try:
        cursor.execute(insert_sql)
    except:
        pass
    con.commit()
    return 1


def update_goods(goods_id, goods_name, goods_price, reserve_first_time, reserve_last_time,
                 pay_first_time, pay_last_time):
    insert_sql = 'update goods set goods_name="{}",goods_price="{}",reserve_first_time="{}",reserve_last_time="{}",pay_first_time="{}",pay_last_time="{}" where goods_id="{}"'.format(
        goods_name, int(goods_price), reserve_first_time, reserve_last_time,
        pay_first_time, pay_last_time, goods_id)
    cursor.execute(insert_sql)
    con.commit()
    return 1


def delete_goods(goods_id, ):
    insert_sql = 'delete from goods where goods_id={}'.format(goods_id)
    cursor.execute(insert_sql)
    con.commit()
    return 1


def update_user_point(mobile, access_token, is_sm, jifen):
    insert_sql = 'update user_login_success set access_token="{}",is_sm="{}",point="{}" where mobile="{}"'.format(
        access_token, is_sm, jifen, mobile
    )
    try:
        cursor.execute(insert_sql)
    except sqlite3.IntegrityError:
        pass
    con.commit()
    return 1


def update_user_sm(mobile, is_sm):
    insert_sql = 'update user_login_success set is_sm="{}" where mobile="{}"'.format(
        is_sm, mobile
    )
    try:
        cursor.execute(insert_sql)
    except sqlite3.IntegrityError:
        pass
    con.commit()
    return 1


def delete_user_by_mobile(mobile):
    insert_sql = 'delete from user_login_success where mobile="{}"'.format(mobile)
    try:
        cursor.execute(insert_sql)
    except sqlite3.IntegrityError:
        pass
    con.commit()
    return 1


def update_user_login_token(mobile, access_token, user_id, is_sm, jifen):
    insert_sql = 'update user_login_success set access_token="{}",user_id="{}",is_sm="{}",point="{}" where mobile="{}"'.format(
        access_token, user_id, is_sm, jifen, mobile
    )
    try:
        cursor.execute(insert_sql)
    except sqlite3.IntegrityError:
        pass
    con.commit()
    return 1


def delete_user_by_moblie(mobile):
    delete_sql = 'delete from user_login_success where mobile ="{}"'.format(
        mobile
    )
    cursor.execute(delete_sql)
    con.commit()
    return 1


def delete_user_by_id(gen_id):
    delete_sql = 'delete from user_login_success where id ={}'.format(
        gen_id
    )
    cursor.execute(delete_sql)
    con.commit()
    return 1


def get_userlogin_mobile(mobile):
    cursor.execute('select * from user_login_success where mobile="{}"'.format(mobile))
    raws = cursor.fetchall()
    if raws:
        return raws[0]
    else:
        return None


def get_userid_mobile_by_token(token):
    cursor.execute('select user_id,mobile from user_login_success where access_token="{}"'.format(token))
    raws = cursor.fetchall()
    if raws:
        return raws[0]
    else:
        return None


def get_token_by_mobile(mobile):
    cursor.execute('select access_token,user_id from user_login_success where mobile="{}"'.format(mobile))
    raws = cursor.fetchall()
    if raws:
        return raws[0][0]
    else:
        return ''


def get_rand_token():
    cursor.execute('select access_token,user_id from user_login_success')
    raws = cursor.fetchall()
    data = []
    for i in raws:
        if len(i[0]) == 64:
            data.append(i)
    if data:
        res = random.choice(data)
    else:
        res = '', ''
    # print(res)
    return res


def init_db():
    sql1 = '''CREATE TABLE "goods" (
            "goods_id"	INTEGER NOT NULL UNIQUE,
            "goods_name"	TEXT,
            "goods_price"	INTEGER,
            "reserve_first_time"	TEXT,
            "reserve_last_time"	TEXT,
            "pay_first_time"	TEXT,
            "pay_last_time"	TEXT,
            PRIMARY KEY("goods_id")
        )'''
    sql2 = '''
            CREATE TABLE "proxy" (
            "order"	TEXT NOT NULL UNIQUE,
            "id"	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
            "api_key"	TEXT NOT NULL UNIQUE,
            "order_type"	TEXT
        )'''
    sql3 = '''
        CREATE TABLE "user_login_success" (
        "id"	INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
        "mobile"	TEXT NOT NULL UNIQUE,
        "access_token"	TEXT NOT NULL,
        "user_id"	TEXT,
        "is_sm"	TEXT DEFAULT '否',
        "point"	TEXT
    )'''
    sql4 = '''
        CREATE TABLE "user_yuyue_success" (
            "mobile"	INTEGER,
            "id"	INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,
            "activity_id"	INTEGER,
            "goods_id"	INTEGER,
            "quantity"	INTEGER,
            "addr_id"	INTEGER,
            "pay_first_time"	TEXT,
            "pay_last_time"	TEXT,
            "is_qj_succ"	TEXT,
            "qj_start_time"	TEXT
        )'''
    try:
        cursor.execute(sql1)
        cursor.execute(sql2)
        cursor.execute(sql3)
        cursor.execute(sql4)
        con.commit()
    except Exception as e:
        print(e)
        pass


if __name__ == '__main__':
    init_db()
    # create_user_login_success('18323680105', '9b02866ac973046e8775c7bea6c2fd2694ada891ee20c4face19aa755bbf999f','333207')
    # delete_user_by_moblie('18323680105')
    # delete_user_by_id(1)
    # a = get_userid_mobile_by_token('1126b0fdc9fb1239f26f6d85a472bda6718311f2e3aab76fef214602fa09cadd')
    # b = get_rand_token()
    # print(a)
    # print(b)
    # update_user_login_token("18323680105",'123')
    # update_user_login_token('18323680105', '1194955f7bd99b3b574ae7390797d51594ada891ee20c4face19aa755bbf999f')
    # create_user_login_success('18323680105', '1194955f7bd99b3b574ae7390797d51594ada891ee20c4face19aa755bbf999f', '333207','否',12)
